﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_AdventureWorksScripts Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_AdventureWorksScripts Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>In SQL Server, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the <b>AdventureWorks</b> sample database. </p>
  <h1 class="heading">Scenario</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">In SQL Server, tables and other schema-scoped objects are contained in schemas, and the schemas are owned by users. In <b>AdventureWorks</b>, five schemas are used to contain schema-scoped objects that are based on business functionality. For example, customer and sales-related objects are contained in the <b>Sales</b> schema; employee-related objects are contained in the <b>HumanResources</b> schema, and so on. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.</p>
      <p xmlns="">The addition of schemas introduces changes to the way in which you access tables and other schema-scoped objects. To access objects in a schema other than the default schema (<b>DB</b>), at a minimum, a two-part identifier in the form <i>schema_name</i><b>.</b><i>object_name</i> must be specified. This is true for all DDL and DML statements that reference schema-scoped objects.</p>
      <p xmlns="">The scripts included in this sample provide alternatives to working with schema-scoped objects. One alternative transfers all schema-scoped objects to the <b>dbo</b> schema and the other creates synonyms for each schema-scoped object in the <b>dbo</b> schema. </p>
    </content></div><h1 class="heading">Languages</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">
        Transact-SQL
      </p>
    </content></div><h1 class="heading">Features</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The <b>AdventureWorks</b> scripts use the following features of the Database Engine.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Application area
            </th>
            <th>
              Features
            </th>
          </tr><tr>
          <td>
            <p>
              Database Engine
            </p>
          </td>
          <td>
            <p>Schema DDL </p>
          </td>
        </tr><tr>
          <td>
            <p>
              Database Engine
            </p>
          </td>
          <td>
            <p>Synonyms</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Prerequisites</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Before you run this sample, perform the following tasks:</p>
      <ul xmlns=""><li>
          Install SQL Server and make sure you include the following components:<br />
          <ul><li>
              The Database Engine<br />
            </li><li>
              
                SQL Server Management Studio
              <br />
            </li></ul>
        </li><li>
          Download the <b>AdventureWorks</b> (OLTP) sample database and Database Engine samples available at the <a href="http://go.microsoft.com/fwlink/?linkid=100108" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Microsoft SQL Server Samples and Community Projects Web site</linkText></a>.<br />
        </li></ul>
    </content></div><h1 class="heading">Transferring Objects to the dbo Schema</h1><div id="sectionSection4" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The AlterSchemaToDbo.sql script transfers every schema-scoped object in <b>AdventureWorks</b> to the <b>dbo</b> schema. After this script has been run, users who have a default schema of <b>dbo</b> will not have to use a two-part identifier when they reference these objects in DDL and DML statements.</p>
      <div class="alert" xmlns=""><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
        The code examples and samples provided with SQL Server will not run after the AlterSchemaToDbo.sql is executed unless the schema names specified in the code are replaced with <b>dbo</b> or removed. <p />
      </td></tr></table><p /></div>
      <p xmlns="">The AlterSchemaFromDbo.sql script transfers (returns) the objects from the <b>dbo</b> schema to the schemas they were in before running the AlterSchemaToDbo.sql script.</p>
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <h3 class="subHeading" xmlns="">Running the Scripts to Transfer Objects to and from the dbo Schema</h3><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">The following procedure shows how to run the Transact-SQL script that transfers <b>AdventureWorks</b> schema-scoped objects to the <b>dbo</b> schema. </p>
          <h4 class="procedureHeading" xmlns="">To run the AlterSchemaToDbo script</h4><div id="procedureSectionEFBBBHBHA" class="section" xmlns=""><ul><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In SQL Server Management Studio, open the file AlterSchemaToDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click <b>Execute</b>.</p>
                </content>
              </li></ul></div>
          <p xmlns="">The following procedure shows how to run the Transact-SQL script that returns <b>AdventureWorks</b> schema-scoped objects from the <b>dbo</b> schema to their original schemas. </p>
          <h4 class="procedureHeading" xmlns="">To run the AlterSchemaFromDbo scripts</h4><div id="procedureSectionEBBBBHBHA" class="section" xmlns=""><ul><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In SQL Server Management Studio, open the file AlterSchemaFromDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click <b>Execute</b>.</p>
                </content>
              </li></ul></div>
        </content></div>
    </sections></div><h1 class="heading">Using Synonyms</h1><div id="sectionSection5" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">A synonym is an alternative name given to a schema-scoped object. The synonym is specified instead of the base object in DDL and DML statements. </p>
      <p xmlns="">The CreateSynonymsDbo.sql script creates a synonym for each schema-scoped object in <b>AdventureWorks</b>. The synonym name is the same as the base object name, but uses the <b>dbo</b> schema. For example, the synonym for <b>HumanResources.Department </b>is <b>dbo.Department</b>. </p>
      <p xmlns="">Using synonyms has the following advantages:</p>
      <ul xmlns=""><li>
          If <b>dbo</b> is the default schema, a two-part identifier is not needed to specify these objects in DDL and DML statements. <br />
        </li><li>
          The code examples and samples provided with SQL Server can be used without modification.<br />
        </li></ul>
      <p xmlns="">The DropSynonymsDbo.sql script drops the synonyms that are created by the CreateSynonymsDbo.sql script. </p>
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <h3 class="subHeading" xmlns="">Running the Synonym Scripts</h3><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">The following procedure shows how to run the Transact-SQL script that creates synonyms in the <b>dbo</b> schema for each <b>AdventureWorks</b> schema-scoped object. </p>
          <h4 class="procedureHeading" xmlns="">To run the CreateSynonymsDbo script</h4><div id="procedureSectionEFBBBFBHA" class="section" xmlns=""><ul><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In SQL Server Management Studio, open the file CreateSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click <b>Execute</b>.</p>
                </content>
              </li></ul></div>
          <p xmlns="">The following procedure shows how to run the Transact-SQL script that removes the synonyms that are created by the CreateSynonymsDbo.sql script from the database. </p>
          <h4 class="procedureHeading" xmlns="">To run the DropSynonymsDbo script</h4><div id="procedureSectionEBBBBFBHA" class="section" xmlns=""><ul><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In SQL Server Management Studio, open the file DropSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click <b>Execute</b>.</p>
                </content>
              </li></ul></div>
        </content></div>
    </sections></div><h1 class="heading">Removing the Scripts</h1><div id="sectionSection6" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Use the following procedure to remove one or more scripts that are included in this sample.</p>
      <h4 class="procedureHeading" xmlns="">To remove the scripts</h4><div id="procedureSectionEBBDBHA" class="section" xmlns=""><ul><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In Windows Explorer, locate the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, right-click the script name and click <b>Delete</b>.</p>
            </content>
          </li></ul></div>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>